package com.iplatform.file.function.meta.utils;

/**
 * @author Dair
 * @since
 */

import cn.hutool.json.JSONUtil;
import lombok.SneakyThrows;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class ExcelUtil {
	
	
	private static final String XLS = "xls";
	private static final String XLSX = "xlsx";
	
	
	private static final String[] titleArr = {"情况表", "统计表", "总表", "项目表", "明细表", "决算表", "平衡表"};
	
	
	private static final String[] otherArr = {"表", "单位：", "财决"};
	
	
	private static final String[] explainArr = {"说明：", "注："};
	
	private static final String[] sheetArr = {"封面", "目录"};
	
	
	private static final String[] unitArr = {"编制单位", "金额单位"};
	
	private static final String[] specialArr = {"类", "款", "项", "目"};
	
	
	/**
	 * 导出Excel
	 *
	 * @param sheetName sheet名称
	 * @param title     标题
	 * @param values    内容
	 * @param wb        HSSFWorkbook对象
	 * @return
	 */
	public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
		
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if (wb == null) {
			wb = new HSSFWorkbook();
		}
		
		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
		
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow row = sheet.createRow(0);
		
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
//		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		
		//声明列对象
		HSSFCell cell = null;

//		//创建标题
//		for (int i = 0; i < title.length; i++) {
//			cell = row.createCell(i);
//			cell.setCellValue(title[i]);
//			cell.setCellStyle(style);
//		}
		
		//创建内容
		for (int i = 0; i < values.length; i++) {
			row = sheet.createRow(i + 1);
			if (values[i] != null) {
				for (int j = 0; j < values[i].length; j++) {
					//将内容按顺序赋给对应的列对象
					row.createCell(j).setCellValue(values[i][j]);
				}
			} else {
				continue;
			}
			
		}
		return wb;
	}
	
	
	/**
	 * 将流中的Excel数据转成List<Map>(读取Excel)
	 *
	 * @param in       输入流
	 * @param fileName 文件名（判断Excel版本）
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, Object>> readExcel(InputStream in, String fileName) throws Exception {
		// 根据文件名来创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		List<Map<String, Object>> resultList = new ArrayList<>();
		
		//循环多个工作表
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			List<String[][]> resultDataList = new ArrayList<>();
			Map<String, Object> sheetMap = new HashMap<>(16);
			
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			if (ArrayUtils.contains(sheetArr, sheet.getSheetName())) {
				continue;
			}
			//判断是否有特殊的单元格
			dealWithSpecialCell(sheet);
			//获取有合并单元格的区域
			List<CellRangeAddress> combineCellList = getCombineCellList(sheet);
			
			
			// 测试有几行数据是有表头数据的
			boolean flag = true;
			int h = 0;
			int rowEnd = sheet.getLastRowNum();
			
			int maxCellNum = 0;
			//获取最大列
			for (int rowNum = 0; rowNum < rowEnd; rowNum++) {
				row = sheet.getRow(rowNum);
				
				if (row == null) {
					continue;
				}
				int cellNum = 0;
				try {
					cellNum = row.getLastCellNum();
				} catch (Exception e) {
				}
				if (cellNum > maxCellNum) {
					maxCellNum = cellNum;
				}
			}
			for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1 && flag; j++) {
				row = sheet.getRow(j);
				if (row == null) {
					break;
				}
				
				// 遍历所有的列
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					Object v = getCellValue(cell);
					
					if (v != null) {
						StringBuilder stringBuilder = new StringBuilder("^");
						for (String s : explainArr) {
							stringBuilder.append("");
							stringBuilder.append(s);
							stringBuilder.append(".*|");
						}
						String substring = stringBuilder.substring(0, stringBuilder.lastIndexOf("|"));
						Pattern p = Pattern.compile(substring);
						Matcher m = p.matcher(v.toString());
						if (m.matches()) {
							flag = false;
							break;
						} else if (!m.matches() && !v.toString().equals("")) {
							flag = true;
							break;
						}
					} else {
						flag = false;
					}
					
				}
				h++;
			}
			//表头行数
			int h1 = h - 1;
			int rowHeaderIndex = 0;
			boolean checkFlag = true;
			String[][] arr = new String[h1][maxCellNum];
			//循环行，不要从0开始，防止前几行为空的情况
			for (int k = sheet.getFirstRowNum(); k < sheet.getFirstRowNum() + h1; k++) {
				row = sheet.getRow(k);
				
				//判断该行是否只有一条数据，是否匹配表、单位：
				boolean matchFlag = matchFirstStringAndRowIsOnlyOne(otherArr, row);
				boolean matchTitleFlag = matchLastStringAndRowIsOnlyOne(titleArr, row);
				boolean matchUnitFlag = matchFirstString(unitArr, row);
				if (!matchFlag && !matchTitleFlag && !matchUnitFlag) {
					// 遍历所有的列
					boolean notCombined = true;
					for (int x = row.getFirstCellNum(); x < row.getLastCellNum(); x++) {
						cell = row.getCell(x);
						//判断该cell是否为合并单元格中的一个
						Map<String, Object> isCombined = isCombineCell(combineCellList, cell, sheet);
						if (isCombined != null && isCombined.get("flag") != null && (Boolean) isCombined.get("flag")) {
							int x1 = (int) isCombined.get("x1");
							int x2 = (int) isCombined.get("x2");
							if (x1 != x2) {
								notCombined = false;
							}
						}
					}
					if (checkFlag && notCombined && k + 1 < sheet.getFirstRowNum() + h1 && !isCombinedRow(sheet.getRow(k + 1), combineCellList, sheet)) {
						checkFlag = false;
						rowHeaderIndex = k + 1;
					}
				}
				arr = convertRowToData(row, arr, k);
			}
			resultDataList.add(arr);
			
			HashSet<ExcleCellInfo> excleCellInfoMap = new HashSet<>();
			
			HashMap<String,String> hashMap = new HashMap<>();
			//循环行，不要从0开始，防止前几行为空的情况
			for (int k = sheet.getFirstRowNum(); k < sheet.getFirstRowNum() + h1 && k < rowHeaderIndex; k++) {
				row = sheet.getRow(k);
				if (row == null) {
					continue;
				}
				for (int x = row.getFirstCellNum(); x < row.getLastCellNum(); x++) {
					cell = row.getCell(x);
					//表格cell的数据，合并的只有左上角一个有数据，其余全为空
					Object o = getCellValue(cell);
					String v = "";
					if (o != null) {
						v = o.toString().trim();
					}
					Map<String, Object> isCombined = isCombineCell(combineCellList, cell, sheet);
					ExcleCellInfo excleCellInfo = new ExcleCellInfo();
					if (!StringUtils.isEmpty(v)) {
						excleCellInfo.setValue(v.trim());
					}
					if (isCombined != null && isCombined.get("flag") != null && (Boolean) isCombined.get("flag")) {
						excleCellInfo.setX1((int) isCombined.get("x1"));
						excleCellInfo.setX2((int) isCombined.get("x2"));
						excleCellInfo.setY1((int) isCombined.get("y1"));
						excleCellInfo.setY2((int) isCombined.get("y2"));
						if(!org.apache.commons.lang3.StringUtils.isBlank(excleCellInfo.getValue())){
							hashMap.put("["+excleCellInfo.getX1()+","+excleCellInfo.getX2()+","+excleCellInfo.getY1()+","+excleCellInfo.getY2()+"]",excleCellInfo.getValue());
						}
					} else {
						excleCellInfo.setX1(x);
						excleCellInfo.setX2(x);
						excleCellInfo.setY1(k);
						excleCellInfo.setY2(k);
					}
					String value = hashMap.get("[" + excleCellInfo.getX1() + "," + excleCellInfo.getX2() + "," + excleCellInfo.getY1() + "," + excleCellInfo.getY2() + "]");
					if(!org.apache.commons.lang3.StringUtils.isBlank(value)){
						excleCellInfo.setValue(value);
					}
					excleCellInfoMap.add(excleCellInfo);
				}
			}
			sheetMap.put("rowHeaderIndex", rowHeaderIndex);
			sheetMap.put("sheetName", sheet.getSheetName());
			sheetMap.put("headerList", excleCellInfoMap);
			sheetMap.put("resultDataList", resultDataList);
			resultList.add(sheetMap);
		}
		return resultList;
		
	}
	
	
	private static void dealWithSpecialCell(Sheet sheet) throws Exception {
		Row row = null;
		Cell cell = null;
		
		// 遍历所有的列
		boolean specialFlag = false;
		List<Integer> startCellNum = new ArrayList<>();
		int startRowNum = 0;
		
		//获取有合并单元格的区域
		List<CellRangeAddress> combineCellList = getCombineCellList(sheet);
		for (int k = sheet.getFirstRowNum(); k < sheet.getLastRowNum() && !specialFlag; k++) {
			row = sheet.getRow(k);
			if (row == null) {
				continue;
			}
			for (int x = row.getFirstCellNum(); x < row.getLastCellNum(); x++) {
				cell = row.getCell(x);
				//判断该cell是否为合并单元格中的一个
				Map<String, Object> isCombined = isCombineCell(combineCellList, cell, sheet);
				if (isCombined != null && isCombined.get("flag") != null && (Boolean) isCombined.get("flag")) {
					Object v = getCellValue(cell);
					if (v != null) {
						//判断是否包含
						if (specialArr[0].equals(v)) {
							startCellNum.add(x);
							if (x + 1 < row.getLastCellNum()) {
								Cell cell1 = row.getCell(x + 1);
								Object object1 = getCellValue(cell1);
								if (object1 != null) {
									if (specialArr[1].equals(object1)) {
										startCellNum.add(x + 1);
										if (x + 2 < row.getLastCellNum()) {
											Cell cell2 = row.getCell(x + 2);
											Object object2 = getCellValue(cell2);
											if (object2 != null) {
												if (specialArr[2].equals(object2)) {
													startCellNum.add(x + 2);
													specialFlag = true;
													startRowNum = k;
													if (x + 3 < row.getLastCellNum()) {
														Cell cell3 = row.getCell(x + 3);
														Object object3 = getCellValue(cell3);
														if (object3 != null) {
															if (specialArr[3].equals(object3)) {
																startCellNum.add(x + 3);
															}
														}
													}
												}
											}
										}
									}
								}
							}
						}
					}
				}
			}
		}
		
		if (specialFlag && !CollectionUtils.isEmpty(startCellNum)) {
			//删除单元格
			for (int i1 = 0; i1 < startCellNum.size(); i1++) {
				removeMergedRegion(sheet, startRowNum, startCellNum.get(i1));
			}
			//删除后面的行
			for (int m = startCellNum.size() - 1; m > 0; m--) {
				//删除该列的其他行单元格,并删除改行后合并
				removeColumnMergedRegionAndMerge(sheet, startCellNum.get(m));
			}
			//修改
			StringBuilder stringBuilder = new StringBuilder();
			for (int m = 0; m < startCellNum.size(); m++) {
				stringBuilder.append(specialArr[m]);
			}
			row.getCell(startCellNum.get(0)).setCellValue(stringBuilder.toString());
		}
	}
	
	/**
	 * Given a sheet, this method deletes a column from a sheet and moves
	 * all the columns to the right of it to the left one cell.
	 * <p>
	 * Note, this method will not update any formula references.
	 *
	 * @param sheet
	 * @param columnToDelete
	 */
	public static void deleteColumn(Sheet sheet, int columnToDelete) {
		int maxColumn = 0;
		for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
			Row row = sheet.getRow(r);
			
			// if no row exists here; then nothing to do; next!
			if (row == null) {
				continue;
				
			}
			
			// if the row doesn't have this many columns then we are good; next!
			int lastColumn = row.getLastCellNum();
			if (lastColumn > maxColumn) {
				maxColumn = lastColumn;
			}
			
			if (lastColumn < columnToDelete) {
				continue;
			}
			
			for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
				Cell oldCell = row.getCell(x - 1);
				if (oldCell != null) {
					row.removeCell(oldCell);
				}
				Cell nextCell = row.getCell(x);
				if (nextCell != null) {
					Cell newCell = row.createCell(x - 1, nextCell.getCellType());
					cloneCell(newCell, nextCell);
				}
			}
		}
		
		// Adjust the column widths
		for (int c = 0; c < maxColumn; c++) {
			sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
		}
	}
	
	/**
	 * 右边列左移
	 *
	 * @param cNew
	 * @param cOld
	 */
	private static void cloneCell(Cell cNew, Cell cOld) {
		cNew.setCellComment(cOld.getCellComment());
		cNew.setCellStyle(cOld.getCellStyle());
		
		if (CellType.BOOLEAN == cNew.getCellTypeEnum()) {
			cNew.setCellValue(cOld.getBooleanCellValue());
		} else if (CellType.NUMERIC == cNew.getCellTypeEnum()) {
			cNew.setCellValue(cOld.getNumericCellValue());
		} else if (CellType.STRING == cNew.getCellTypeEnum()) {
			cNew.setCellValue(cOld.getStringCellValue());
		} else if (CellType.ERROR == cNew.getCellTypeEnum()) {
			cNew.setCellValue(cOld.getErrorCellValue());
		} else if (CellType.FORMULA == cNew.getCellTypeEnum()) {
			cNew.setCellValue(cOld.getCellFormula());
		}
	}
	
	
	public static void removeColumnMergedRegionAndMerge(Sheet sheet, int column) {
		
		HashSet<Map<String, Object>> mapHashSet = new HashSet<>();
		
		for (int row = sheet.getFirstRowNum(); row < sheet.getLastRowNum(); row++) {
			//获取所有的单元格
			int sheetMergeCount = sheet.getNumMergedRegions();
			List<Integer> list = new ArrayList<>();
			for (int i = 0; i < sheetMergeCount; i++) {
				//获取第i个单元格
				CellRangeAddress ca = sheet.getMergedRegion(i);
				int firstColumn = ca.getFirstColumn();
				int lastColumn = ca.getLastColumn();
				
				int firstRow = ca.getFirstRow();
				int lastRow = ca.getLastRow();
				if (row >= firstRow && row <= lastRow) {
					if (column >= firstColumn && column <= lastColumn) {
						list.add(i);
						Map<String, Object> map = new HashMap<>();
						map.put("firstColumn", firstColumn);
						map.put("lastColumn", lastColumn);
						map.put("firstRow", firstRow);
						map.put("lastRow", lastRow);
						mapHashSet.add(map);
					}
				}
			}
			if (!CollectionUtils.isEmpty(list)) {
				list.stream().sorted(Comparator.comparing(Integer::intValue).reversed()).forEach(e -> {
					sheet.removeMergedRegion(e);
				});
			}
		}
		deleteColumn(sheet, column);
		if (!CollectionUtils.isEmpty(mapHashSet)) {
			for (Map<String, Object> map : mapHashSet) {
				int firstColumn = (int) map.get("firstColumn");
				int lastColumn = (int) map.get("lastColumn");
				int firstRow = (int) map.get("firstRow");
				int lastRow = (int) map.get("lastRow");
				if (firstColumn != lastColumn) {
					if (lastColumn - 1 > firstColumn || (lastColumn - 1 == firstColumn && firstRow != lastRow)) {
						try {
							sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn - 1));
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
		
	}
	
	public static void removeMergedRegion(Sheet sheet, int row, int column) {
		//获取所有的单元格
		int sheetMergeCount = sheet.getNumMergedRegions();
		//用于保存要移除的那个单元格序号
		List<Integer> list = new ArrayList<>();
		for (int i = 0; i < sheetMergeCount; i++) {
			//获取第i个单元格
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					list.add(i);
				}
			}
		}
		if (!CollectionUtils.isEmpty(list)) {
			list.stream().sorted(Comparator.comparing(Integer::intValue).reversed()).forEach(e -> {
				sheet.removeMergedRegion(e);
			});
		}
		
	}
	
	private static boolean matchLastStringAndRowIsOnlyOne(String[] regexArr, Row row) {
		boolean flag = true;
		String rowValue = "";
		for (int x = row.getFirstCellNum(); x < row.getLastCellNum() && flag; x++) {
			Cell cell = row.getCell(x);
			Object o = getCellValue(cell);
			String value = "";
			if (o != null) {
				value = o.toString().trim();
			}
			if (value != null && !"".equals(value)) {
				if (!"".equals(rowValue) && !rowValue.equals(value)) {
					flag = false;
				} else {
					rowValue = value;
				}
			}
		}
		if (flag) {
			if (!org.apache.commons.lang.StringUtils.isBlank(rowValue) && regexArr.length > 0) {
				//匹配字段
				StringBuilder stringBuilder = new StringBuilder("");
				for (String s : regexArr) {
					stringBuilder.append(s);
					stringBuilder.append("$|");
				}
				String substring = stringBuilder.substring(0, stringBuilder.lastIndexOf("|"));
				Pattern p = Pattern.compile(substring);
				Matcher m = p.matcher(rowValue);
				if (!m.find()) {
					flag = false;
				}
			} else {
				flag = false;
			}
		}
		return flag;
	}
	
	private static boolean matchFirstString(String[] regexArr, Row row) {
		boolean flag = true;
		for (int x = row.getFirstCellNum(); x < row.getLastCellNum() && regexArr.length > 0; x++) {
			Cell cell = row.getCell(x);
			Object o = getCellValue(cell);
			String value = "";
			if (o != null) {
				value = o.toString().trim();
			}
			if (value != null && !"".equals(value)) {
				//匹配字段
				StringBuilder stringBuilder = new StringBuilder("^");
				for (String s : regexArr) {
					stringBuilder.append(s);
					stringBuilder.append(".*|");
				}
				String substring = stringBuilder.substring(0, stringBuilder.lastIndexOf("|"));
				Pattern p = Pattern.compile(substring);
				Matcher m = p.matcher(value);
				if (!m.matches()) {
					flag = false;
				} else {
					flag = true;
					break;
				}
			}
		}
		return flag;
	}
	
	private static boolean matchFirstStringAndRowIsOnlyOne(String[] regexArr, Row row) {
		boolean flag = true;
		String rowValue = "";
		for (int x = row.getFirstCellNum(); x < row.getLastCellNum() && flag; x++) {
			Cell cell = row.getCell(x);
			Object o = getCellValue(cell);
			String value = "";
			if (o != null) {
				value = o.toString().trim();
			}
			if (value != null && !"".equals(value)) {
				if (!"".equals(rowValue) && !rowValue.equals(value)) {
					flag = false;
				} else {
					rowValue = value;
				}
			}
		}
		if (flag) {
			if (!org.apache.commons.lang.StringUtils.isBlank(rowValue) && regexArr.length > 0) {
				//匹配字段
				StringBuilder stringBuilder = new StringBuilder("^");
				for (String s : regexArr) {
					stringBuilder.append(s);
					stringBuilder.append(".*|");
				}
				String substring = stringBuilder.substring(0, stringBuilder.lastIndexOf("|"));
				Pattern p = Pattern.compile(substring);
				Matcher m = p.matcher(rowValue);
				if (!m.matches()) {
					flag = false;
				}
			} else {
				flag = false;
			}
		}
		return flag;
	}
	
	@SneakyThrows
	private static boolean isCombinedRow(Row row, List<CellRangeAddress> combineCellList, Sheet sheet) {
		boolean flag = false;
		for (int x = row.getFirstCellNum(); x < row.getLastCellNum(); x++) {
			Cell cell = row.getCell(x);
			//判断该cell是否为合并单元格中的一个
			Map<String, Object> isCombined = isCombineCell(combineCellList, cell, sheet);
			if (isCombined != null && isCombined.get("flag") != null && (Boolean) isCombined.get("flag")) {
				flag = true;
				break;
			}
		}
		return flag;
	}
	
	//获取合并单元格集合
	public static List<CellRangeAddress> getCombineCellList(Sheet sheet) {
		List<CellRangeAddress> list = new ArrayList<>();
		//获得一个 sheet 中合并单元格的数量
		//遍历所有的合并单元格
		int sheetmergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetmergerCount; i++) {
			//获得合并单元格保存进list中
			CellRangeAddress ca = sheet.getMergedRegion(i);
			list.add(ca);
		}
		return list;
	}
	
	/**
	 * 判断cell是否为合并单元格，是的话返回合并行数和列数（只要在合并区域中的cell就会返回合同行列数，但只有左上角第一个有数据）
	 *
	 * @param listCombineCell 上面获取的合并区域列表
	 * @param cell
	 * @param sheet
	 * @return
	 * @throws Exception
	 */
	public static Map<String, Object> isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell, Sheet
			sheet)
			throws Exception {
		Map<String, Object> result = new HashMap<>(16);
		if (cell == null) {
			return result;
		}
		int firstC = 0;
		int lastC = 0;
		int firstR = 0;
		int lastR = 0;
		String cellValue = null;
		Boolean flag = false;
		int mergedRow = 0;
		int mergedCol = 0;
		result.put("flag", flag);
		for (CellRangeAddress ca : listCombineCell) {
			//获得合并单元格的起始行, 结束行, 起始列, 结束列
			firstC = ca.getFirstColumn();
			lastC = ca.getLastColumn();
			firstR = ca.getFirstRow();
			lastR = ca.getLastRow();
			//判断cell是否在合并区域之内，在的话返回true和合并行列数
			if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
				if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
					flag = true;
					mergedRow = lastR - firstR + 1;
					mergedCol = lastC - firstC + 1;
					result.put("flag", true);
					result.put("mergedRow", mergedRow);
					result.put("mergedCol", mergedCol);
					result.put("x1", firstC);
					result.put("x2", lastC);
					result.put("y1", firstR);
					result.put("y2", lastR);
					break;
				}
			}
		}
		return result;
	}
	
	/**
	 * 根据文件后缀名类型获取对应的工作簿对象
	 *
	 * @param inputStream 读取文件的输入流
	 * @return 包含文件数据的工作簿对象
	 * @throws IOException
	 */
	public static Workbook getWorkbook(InputStream inputStream, String fileName) throws IOException {
		Workbook workbook = null;
		if (fileName.endsWith(XLS) || fileName.endsWith(XLS.toUpperCase())) {
			workbook = new HSSFWorkbook(inputStream);
		} else if (fileName.endsWith(XLSX) || fileName.endsWith(XLSX.toUpperCase())) {
			workbook = new XSSFWorkbook(inputStream);
		}
		return workbook;
	}
	
	
	/**
	 * 将单元格内容转换为字符串
	 *
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		Object returnValue = null;
		//数字
		if (cell.getCellTypeEnum() == CellType.NUMERIC) {
			Double doubleValue = cell.getNumericCellValue();
			// 格式化科学计数法，取一位整数
			DecimalFormat df = new DecimalFormat("0");
			returnValue = df.format(doubleValue);
		} else if (cell.getCellTypeEnum() == CellType.STRING) {
			//字符串
			returnValue = cell.getStringCellValue();
		} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
			//布尔
			Boolean booleanValue = cell.getBooleanCellValue();
			returnValue = booleanValue.toString();
		} else if (cell.getCellTypeEnum() == CellType.BLANK) {
			//空值
		} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
			//公式
			try {
				returnValue = String.valueOf(cell.getStringCellValue());
			} catch (IllegalStateException e) {
				returnValue = String.valueOf(cell.getNumericCellValue());
			}
			
		} else if (cell.getCellTypeEnum() == CellType.ERROR) {
		
		}
		return returnValue;
	}
	
	private static String[][] convertRowToData(Row row, String[][] arr, int rowIndex) {
		
		Cell cell;
		
		int lastCellNum = row.getLastCellNum();
		
		for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
			cell = row.getCell(cellNum);
			if (cell == null) {
				continue;
			}
			Object cellValue = getCellValue(cell);
			arr[rowIndex][cellNum] = cellValue != null ? (String) cellValue : null;
		}
		return arr;
	}
	
	public static void main(String[] args) throws Exception {
		
		File file = new File("E:\\代锐的文件\\预决算数据报表 (3)\\预决算数据报表\\3-决算数据\\2-中国人民政治协商会议四川省委员会办公厅1.XLS");
		FileInputStream fis = new FileInputStream(file);
		List<Map<String, Object>> maps = readExcel(fis, file.getName());
		System.out.println(maps.toString());
		System.out.println(JSONUtil.toJsonStr(maps.get(0).get("headerList")));
		System.out.println(JSONUtil.toJsonStr(maps.get(0).get("resultDataList")));
		
		
	}
	
	
}